﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using LanDBom.Models;
using Webdiyer.WebControls.Mvc;
using System.Data.Entity;
using Newtonsoft.Json;
using LanDBom.Models.Procure;
using System.Web.UI.WebControls;
using System.IO;
using NPOI.SS.UserModel;
using OfficeOpenXml;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.tool.xml;
using LanDBom.Models.Price_sheet;
using Org.BouncyCastle.Asn1.Cmp;
using System.Text;
using Newtonsoft.Json.Linq;
using LanDBom.Models.K3APIhelp;
using Kingdee.BOS.WebApi.Client;
using System.Web.Http.Results;
using Image = iTextSharp.text.Image;
using Kingdee.BOS.WebApi.DataEntify;
using LanDBom.Models.price_New_2;
using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using static System.Windows.Forms.AxHost;
using LanDBom.Models.Contracts;
using FastReport.Barcode;
using MathNet.Numerics.Distributions;
using System.Globalization;
using System.Threading;
using FastReport;
using System.Text.RegularExpressions;
using Kingdee.BOS.Util;
using iTextSharp.tool.xml.html.head;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Formula.Functions;
using System.Data.SqlClient;
using System.Web.DynamicData;

namespace LanDBom.Controllers
{
    public class HomeController : Controller
    {
        private LDbaseEntities db = new LDbaseEntities();
        Find_P FP = new Find_P();
        Find_APP_information find_APP = new Find_APP_information();
        public ActionResult Index(string AP_bian = "", string App_Kehu = "", string App_JTime = "", int states = 0, int shund = 0, int Zhuagn = 0, string BB_SQ_xmmc = null, string BB_SQ_khmc = null, string BB_SQ_sqrq = null, string BB_SQ_gjz = null, string jssj = null, string qssj = null, string danhao = null, string xmlx = null, string xmjd = null, string jzrq = null, string xmjs = null, string MRRY = null, string Sheji = null)
        {
            List<Applications> app = db.Applications.Where(s => s.AP_bian.Contains("SQ") && s.AP_QTJY == null).OrderByDescending(s => s.App_id).ToList();
            var listApp = find_APP.find_intsr(BB_SQ_xmmc, BB_SQ_khmc, BB_SQ_gjz, jssj, qssj, danhao, xmlx, xmjd, jzrq, xmjs, MRRY, Sheji);
            var HNFJ = listApp.Count();
            listApp = listApp.OrderByDescending(ss => DateTime.Parse(ss.App_qishi)).ToList();
            listApp = listApp.Skip(shund * 10).Take(10).ToList();
            ViewBag.MRRRY = db.Dengli.Where(s => s.DE_bumeng == 1).ToList();
            if (states == 1)
            {
                //前台计算移到后台
                foreach (var item in listApp)
                {
                    switch (item.App_bu)
                    {
                        case "1":
                            var sjnf = db.Designdraw.Where(x => x.D_Ton == item.AP_bian).FirstOrDefault();
                            string zhuang1 = "";
                            if (sjnf != null)
                            {
                                zhuang1 = sjnf.D_zhuang;
                                if (zhuang1 == null)
                                {
                                    zhuang1 = "100";
                                    var husnff = db.Applications.Where(s => s.AP_bian == item.AP_bian).FirstOrDefault().App_bei;
                                    if (husnff == "保存")
                                    {
                                        item.App_bu = "101";
                                    }
                                }
                                else if (zhuang1 == "2")
                                {
                                    item.APP_liy = "等待设计主管分配任务";
                                }
                                else if (zhuang1 == "3")
                                {
                                    var list1 = db.ShejinBeiZhu.Where(x => x.SJ_Bian == item.AP_bian && x.SJ_ZT != "完成").ToList();
                                    string temp = "";
                                    if (list1.Count == 0)
                                    {
                                        item.APP_liy = "已经全部递交，等待设计主管审核";
                                    }
                                    else
                                    {
                                        foreach (ShejinBeiZhu sjz in list1)
                                        {
                                            temp += sjz.Dengli.DE_nae + " ";
                                        }
                                        item.APP_liy = "等待" + temp + "递交设计图";
                                    }
                                }
                                else if (zhuang1 == "4")
                                {
                                    item.APP_liy = "等待" + item.AP_ren + "审核";
                                }
                                else if (zhuang1 == "5")
                                {
                                    item.APP_liy = "设计委托完成";
                                }
                            }
                            else
                            {
                                var sjnf2 = db.Applications.Where(x => x.AP_bian == item.AP_bian).FirstOrDefault();
                                if (sjnf2 != null)
                                {
                                    if (sjnf2.App_bei == "保存")
                                    {
                                        item.APP_liy = "保存中，还未正式提交";
                                    }
                                    else
                                    {
                                        item.APP_liy = "等待" + item.AP_ren + "提交到BOM重置";
                                    }
                                }
                            }
                            break;
                        case "2":
                            item.APP_liy = "等待" + item.AP_ren + "提交到工艺";
                            break;
                        case "3":
                            string temp3 = "";
                            string index3 = "";
                            int num33 = db.BOMini.Where(x => x.BOM2_bian == AP_bian && x.BOM2_Lei == "自制").Count();
                            int num23 = db.BOMini.Where(x => x.BOM2_bian == AP_bian && x.BOM2_Lei == "外购").Count();
                            var list3 = db.BOMini.Where(x => x.BOM2_bian == AP_bian && x.BOM2_sunhao != "已询价" && x.BOM2_Lei == "外购").ToList();

                            List<BOMini> list23 = new List<BOMini>();
                            var iindex = 0;
                            string zhuang = "";
                            foreach (BOMini b in list3)
                            {
                                var jeiji = b.BOM2_BQing;
                                int twwe = list3.Where(x => x.BOM2_BQing != jeiji && x.BOM2_BQing.Contains(jeiji)).Count();
                                if (twwe == 0)
                                {
                                    list23.Add(b);
                                }
                            }

                            if (list23 != null)
                            {
                                foreach (BOMini b in list23)
                                {
                                    var ST = b.BOM2_ST;
                                    string temp2 = db.duty.Where(x => x.du_name == ST).FirstOrDefault().Dengli.DE_nae;
                                    if (temp3.Contains(temp2) != true)
                                    {
                                        temp3 += temp2 + " ";
                                    }
                                }
                            }
                            if (num33 == 0)
                            {
                                item.APP_liy = "等待采购：" + temp3 + "进行物料询价/工艺配置";
                            }
                            else if (num23 == 0)
                            {
                                item.APP_liy = "等待销售:" + item.AP_ren + "进行工艺配置";
                            }
                            else
                            {
                                item.APP_liy = "等待销售:" + item.AP_ren + "等待采购：" + temp3 + "进行物料询价/工艺配置";
                            }
                            break;
                        case "4":
                            var lid = db.BOMini.Where(x => x.BOM2_bian == AP_bian && x.BOM2_Lei == "外购").ToList();
                            List<BOMini> list2 = new List<BOMini>();
                            foreach (BOMini b in lid)
                            {
                                var jeiji = b.BOM2_BQing;
                                int twwe = lid.Where(x => x.BOM2_BQing != jeiji && x.BOM2_BQing.Contains(jeiji)).Count();
                                if (twwe == 0)
                                {
                                    list2.Add(b);
                                }

                            }
                            int num = list2.Count();
                            int num2 = list2.Where(x => x.BOM2_sunhao == "已询价").Count();

                            if (num == num2)
                            {
                                string temp = "";
                                string index = "";

                                int num3 = db.BOMini.Where(x => x.BOM2_bian == AP_bian && x.BOM2_Lei == "自制").Count();
                                int num4 = db.BOMini.Where(x => x.BOM2_bian == AP_bian && x.BOM2_Lei == "外购").Count();
                                var fef = db.Applications.Where(x => x.AP_bian == AP_bian).FirstOrDefault();
                                if (fef == null)
                                {
                                    index = "";
                                }
                                else
                                {
                                    index = fef.AP_ren;
                                }


                                foreach (BOMini b in list2)
                                {
                                    var ST = b.BOM2_ST;
                                    string temp2 = db.duty.Where(x => x.du_name == ST).FirstOrDefault().Dengli.DE_nae;

                                    if (temp.Contains(temp2) != true)
                                    {
                                        temp += temp2 + " ";
                                    }

                                }
                                if (num3 == 0)
                                {
                                    item.APP_liy = "等待采购：" + temp + "进行工艺配置";
                                }
                                else if (num4 == 0)
                                {
                                    item.APP_liy = "等待销售:" + index + "进行工艺配置";
                                }
                                else
                                {
                                    item.APP_liy = "等待销售:" + index + "等待采购：" + temp + "进行工艺配置";
                                }
                            }
                            else
                            {
                                string temp = "";
                                var listww = list2.Where(x => x.BOM2_sunhao != "已询价").ToList();
                                foreach (BOMini b in listww)
                                {
                                    var ST = b.BOM2_ST;
                                    string temp2 = db.duty.Where(x => x.du_name == ST).FirstOrDefault().Dengli.DE_nae;
                                    if (temp.Contains(temp2) != true)
                                    {
                                        temp += temp2 + " ";
                                    }
                                }
                                item.APP_liy = "等待采购：" + temp + "进行物料询价";
                            }

                            break;
                        case "5":
                            var spl2 = db.shenpingliu.Where(x => x.FJINF == AP_bian).FirstOrDefault();

                            if (spl2 == null)
                            {
                                item.APP_liy = "等待核价";
                            }
                            else
                            {
                                if (spl2.FFZ != "通过")
                                {
                                    item.APP_liy = "等待" + spl2.FFirst + "审核";
                                }
                                else
                                {
                                    if (spl2.FTZ != "通过")
                                    {
                                        item.APP_liy = "等待" + spl2.FTwo + "审核";
                                    }
                                }
                            }
                            break;
                        case "6":
                            var spl = db.shenpingliu.Where(x => x.FJINF == item.AP_bian && x.FMIao == "对外报价").FirstOrDefault();

                            if (spl == null)
                            {
                                item.APP_liy = "等待" + item.AP_ren + "对外报价";
                            }
                            else
                            {
                                if (spl.FFZ != "通过")
                                {
                                    item.APP_liy = "等待" + spl.FFirst + "审核";
                                }
                            }

                            break;
                        case "7":
                            item.APP_liy = "等待" + item.AP_ren + "生成报价单";
                            break;
                        case "-99":
                            item.APP_liy = "任务结束";
                            break;
                        default:
                            break;
                    }
                    var date2 = DateTime.Parse(item.App_JTime);
                    var date1 = DateTime.Now;
                    if (date2 > date1)
                    {
                        item.App_tuzhi = "0";
                    }
                }
                var list = from s in listApp
                           select new { s.App_id, s.App_Kehu, s.App_qishi, s.App_JTime, s.AP_bian, s.APP_name, s.App_bu, s.APP_fenL, s.App_zhand, s.AP_ren, s.App_xmzt, s.APP_she, s.APP_liy, s.App_tuzhi };
                JsonSerializerSettings settings = new JsonSerializerSettings();
                string jsongStr = JsonConvert.SerializeObject(list, settings);
                return Content(jsongStr, "application/json");
            }
            if (states == 2)
            {
                var index = db.Applications.Where(x => x.AP_bian == AP_bian).FirstOrDefault().AP_ren;
                return Content(index);
            }
            if (states == 4)
            {
                try
                {
                    var A = db.Applications.Where(s => s.AP_bian == AP_bian).ToList();
                    var B = db.BOMini.Where(s => s.BOM2_bian == AP_bian).ToList();
                    var D = db.Designdraw.Where(s => s.D_Ton == AP_bian).ToList();
                    var F = db.shenpingliu.Where(s => s.FJINF == AP_bian).ToList();
                    //删除 
                    int shu = 0, an = 0, JI = 0;
                    foreach (var item in A)
                    {
                        db.Applications.Remove(item);
                        shu += db.SaveChanges();
                    }
                    foreach (var item in D)
                    {
                        db.Designdraw.Remove(item);
                        JI += db.SaveChanges();
                    }
                    foreach (var item in F)
                    {
                        db.shenpingliu.Remove(item);
                        JI += db.SaveChanges();
                    }
                    if (shu == A.Count)
                    {
                        foreach (var item in B)
                        {
                            var C = db.Informa.Where(a => a.BOM2_id == item.BOM2_id).ToList();
                            foreach (var jdin in C)
                            {
                                db.Informa.Remove(jdin);
                            }
                            int s = db.SaveChanges();
                            db.BOMini.Remove(item);
                            an += db.SaveChanges();
                        }
                    }
                    string mess = an == B.Count ? "删除成功" : "删除失败";

                    return Content(mess);
                }
                catch (Exception ex)
                {
                    string messa = ex.Message;
                    return Content(messa);
                }

            }
            if (states == 5)
            {
                var sjnf = db.Designdraw.Where(x => x.D_Ton == AP_bian).FirstOrDefault();
                string zhuang = "";
                if (sjnf != null)
                {
                    zhuang = sjnf.D_zhuang;
                    if (zhuang == null)
                    {
                        zhuang = "100";
                        var husnff = db.Applications.Where(s => s.AP_bian == AP_bian).FirstOrDefault().App_bei;
                        if (husnff == "保存")
                        {
                            zhuang = "101";
                        }
                    }
                    if (zhuang == "3")
                    {
                        var list = db.ShejinBeiZhu.Where(x => x.SJ_Bian == AP_bian && x.SJ_ZT != "完成").ToList();
                        string temp = "";
                        if (list.Count == 0)
                        {
                            zhuang = "已经全部递交，等待设计主管审核";
                        }
                        else
                        {
                            foreach (ShejinBeiZhu sjz in list)
                            {
                                temp += sjz.Dengli.DE_nae + " ";
                            }
                            zhuang = "等待" + temp + "递交设计图";
                        }
                    }
                    if (zhuang == "5")
                    {
                        zhuang = "设计委托完成";
                    }
                }
                else
                {
                    var sjnf2 = db.Applications.Where(x => x.AP_bian == AP_bian).FirstOrDefault();
                    if (sjnf2 != null)
                    {
                        if (sjnf2.App_bei == "保存")
                        {
                            zhuang = "保存中，还未正式提交";
                        }
                    }
                }
                return Content(zhuang);
            }
            if (states == 6)
            {
                var bom = db.BOMini.Where(x => x.BOM2_bian == AP_bian && x.BOM2_sunhao != "已询价").ToList();
                foreach (BOMini b in bom)
                {
                    var name = db.duty.Where(x => x.du_name == b.BOM2_ST).ToList();
                }
            }
            if (states == 7)
            {
                var spl = db.shenpingliu.Where(x => x.FJINF == AP_bian).FirstOrDefault();
                string temp = "";
                if (spl == null)
                {
                    temp = "等待核价";
                }
                else
                {
                    if (spl.FFZ != "通过")
                    {
                        temp = "等待" + spl.FFirst + "审核";
                    }
                    else
                    {
                        if (spl.FTZ != "通过")
                        {
                            temp = "等待" + spl.FTwo + "审核";
                        }
                    }
                }
                return Content(temp);
            }
            if (states == 8)
            {
                var spl = db.shenpingliu.Where(x => x.FJINF == AP_bian && x.FMIao == "对外报价").FirstOrDefault();
                string name = db.Applications.Where(x => x.AP_bian == AP_bian).FirstOrDefault().AP_ren;
                string temp = "";
                if (spl == null)
                {
                    temp = "等待" + name + "对外报价";
                }
                else
                {
                    if (spl.FFZ != "通过")
                    {
                        temp = "等待" + spl.FFirst + "审核";
                    }
                }
                return Content(temp);
            }
            if (states == 9)
            {
                string temp = "";
                string index = "";
                int num = db.BOMini.Where(x => x.BOM2_bian == AP_bian && x.BOM2_Lei == "自制").Count();
                int num2 = db.BOMini.Where(x => x.BOM2_bian == AP_bian && x.BOM2_Lei == "外购").Count();

                index = db.Applications.Where(x => x.AP_bian == AP_bian).FirstOrDefault().AP_ren;
                var list = db.BOMini.Where(x => x.BOM2_bian == AP_bian && x.BOM2_sunhao != "已询价" && x.BOM2_Lei == "外购").ToList();

                List<BOMini> list2 = new List<BOMini>();
                var iindex = 0;
                string zhuang = "";
                foreach (BOMini b in list)
                {
                    var jeiji = b.BOM2_BQing;
                    int twwe = list.Where(x => x.BOM2_BQing != jeiji && x.BOM2_BQing.Contains(jeiji)).Count();
                    if (twwe == 0)
                    {
                        list2.Add(b);
                    }
                }

                if (list2 != null)
                {
                    foreach (BOMini b in list2)
                    {
                        var ST = b.BOM2_ST;
                        string temp2 = db.duty.Where(x => x.du_name == ST).FirstOrDefault().Dengli.DE_nae;
                        if (temp.Contains(temp2) != true)
                        {
                            temp += temp2 + " ";
                        }
                    }
                }
                if (num == 0)
                {
                    return Content("等待采购：" + temp + "进行物料询价/工艺配置");
                }
                else if (num2 == 0)
                {
                    return Content("等待销售:" + index + "进行工艺配置");
                }
                else
                {
                    return Content("等待销售:" + index + "等待采购：" + temp + "进行物料询价/工艺配置");
                }
            }
            if (states == 10)
            {


                var lid = db.BOMini.Where(x => x.BOM2_bian == AP_bian && x.BOM2_Lei == "外购").ToList();
                List<BOMini> list2 = new List<BOMini>();
                foreach (BOMini b in lid)
                {
                    var jeiji = b.BOM2_BQing;
                    int twwe = lid.Where(x => x.BOM2_BQing != jeiji && x.BOM2_BQing.Contains(jeiji)).Count();
                    if (twwe == 0)
                    {
                        list2.Add(b);
                    }

                }
                int num = list2.Count();
                int num2 = list2.Where(x => x.BOM2_sunhao == "已询价").Count();

                if (num == num2)
                {
                    string temp = "";
                    string index = "";

                    int num3 = db.BOMini.Where(x => x.BOM2_bian == AP_bian && x.BOM2_Lei == "自制").Count();
                    int num4 = db.BOMini.Where(x => x.BOM2_bian == AP_bian && x.BOM2_Lei == "外购").Count();
                    index = db.Applications.Where(x => x.AP_bian == AP_bian).FirstOrDefault().AP_ren;

                    foreach (BOMini b in list2)
                    {
                        var ST = b.BOM2_ST;
                        string temp2 = db.duty.Where(x => x.du_name == ST).FirstOrDefault().Dengli.DE_nae;

                        if (temp.Contains(temp2) != true)
                        {
                            temp += temp2 + " ";
                        }

                    }
                    if (num3 == 0)
                    {
                        return Content("等待采购：" + temp + "进行工艺配置");
                    }
                    else if (num4 == 0)
                    {
                        return Content("等待销售:" + index + "进行工艺配置");
                    }
                    else
                    {
                        return Content("等待销售:" + index + "等待采购：" + temp + "进行工艺配置");
                    }
                }
                else
                {
                    string temp = "";
                    var list = list2.Where(x => x.BOM2_sunhao != "已询价").ToList();
                    foreach (BOMini b in list2)
                    {
                        var ST = b.BOM2_ST;
                        string temp2 = db.duty.Where(x => x.du_name == ST).FirstOrDefault().Dengli.DE_nae;
                        if (temp.Contains(temp2) != true)
                        {
                            temp += temp2 + " ";
                        }
                    }
                    return Content("等待采购：" + temp + "进行物料询价");
                }

            }
            if (states == 11)
            {
                var lid = db.BOMini.Where(x => x.BOM2_bian == AP_bian && x.BOM2_Lei == "外购").ToList();
                List<BOMini> list2 = new List<BOMini>();
                var iindex = 0;
                string zhuang = "";
                foreach (BOMini b in lid)
                {
                    var jeiji = b.BOM2_BQing;
                    int twwe = lid.Where(x => x.BOM2_BQing != jeiji && x.BOM2_BQing.Contains(jeiji)).Count();
                    if (twwe == 0)
                    {
                        list2.Add(b);
                    }

                }
                foreach (BOMini b2 in list2)
                {
                    if (b2.BOM2_sunhao != "已询价" && b2.BOM2_sunhao != "已核价")
                    {
                        iindex++;
                    }
                }
                if (iindex == 0)
                {
                    zhuang = "gy";
                }
                else
                {
                    zhuang = "xj";
                }
                return Content(zhuang);
            }
            if (states == 12)
            {
                string infjjj = HNFJ.ToString();
                return Content(infjjj);
            }
            if (states == 13)
            {
                Dengli dengli = db.Dengli.Where(x => x.DE_ID == shund).FirstOrDefault();
                if (dengli != null)
                {
                    dengli.DE_dksj = null;
                    db.SaveChanges();
                }
                return Content("退出");
            }
            if (states == 14)
            {
                Dengli dengli = db.Dengli.Where(x => x.DE_nae == BB_SQ_xmmc && x.DE_Pass == BB_SQ_khmc).FirstOrDefault();
                if (dengli != null)
                {
                    dengli.DE_dksj = "1";
                    db.SaveChanges();
                }
                return Content("登录");
            }
            return View();
        }
        //根据条件查询申请单
        public ActionResult BOMChanffg(string SHun, string AP_bian = "", string App_Kehu = "", string App_JTime = "", int states = 0)
        {
            List<Applications> app = db.Applications.Where(s => s.AP_bian.Contains("SQ") && s.App_bu == SHun && s.AP_QTJY != null).OrderByDescending(s => s.App_qishi).ToList();
            ViewBag.C_App = app;
            var listApp = find_APP.find_app_infor(AP_bian, App_Kehu, App_JTime);
            if (states == 1)
            {
                var list = from s in listApp
                           select new { s.App_id, s.App_Kehu, s.App_qishi, s.App_JTime, s.AP_bian, s.APP_name, s.App_bu, s.APP_fenL, s.App_zhand, s.AP_ren };
                JsonSerializerSettings settings = new JsonSerializerSettings();
                string jsongStr = JsonConvert.SerializeObject(list, settings);
                return Content(jsongStr, "application/json");
            }
            return View();

        }
        //集成查询客户列表
        public ActionResult Contact(int stste = 0)
        {

            return View();
        }
        public class Persom
        {
            public object FName { get; set; }
            public object FIsGroup { get; set; }
            public object FNumber { get; set; }
            public object FCustTypeId { get; set; }
            public object FIsTrade { get; set; }
            public object FRegisterAddress { get; set; }
            public object FIsDefaultConsignee { get; set; }
            public object FDescription { get; set; }
            public object FCompanyClassify { get; set; }
            public object FWEBSITE { get; set; }
            public object FForbidStatus { get; set; }
        }
        public ActionResult HomeView()
        {

            return View();
        }
        public ActionResult DownloadExcel(int state = 0, string excelName = null)
        {
            if (state == 1)
            {
                // 创建Excel文件
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                using (ExcelPackage excel = new ExcelPackage())
                {
                    // 添加工作表
                    var worksheet = excel.Workbook.Worksheets.Add("Sheet1");
                    // 写入数据
                    worksheet.Cells[1, 1].Value = "姓名";
                    worksheet.Cells[1, 2].Value = "年龄";
                    worksheet.Cells[2, 1].Value = "张三";
                    worksheet.Cells[2, 2].Value = 25;
                    worksheet.Cells[3, 1].Value = "李四";
                    worksheet.Cells[3, 2].Value = 30;

                    // 将Excel文件保存到磁盘中
                    string filePath = Server.MapPath("Excel/" + excelName + ".xlsx");
                    FileStream stream = new FileStream(filePath, FileMode.Create);
                    excel.SaveAs(stream);
                    stream.Close();
                }

                // 返回文件下载的响应
                return File("Excel/data.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "数据.xlsx");
            }
            return View();
        }
        Find Find = new Find();
        [ValidateInput(false)]
        public ActionResult About(int state = 0, string Pdfname = null, string htmlContent2 = null, string App_bian = null, string Employee_path = null)
        {
            Session["BJD_App_bian"] = App_bian;
            string filePath = null;
            if (state == 1)
            {
                if (Employee_path != "")
                {
                    Employee_path = Employee_path.Replace(" ", string.Empty);
                    Employee_path = Employee_path.Substring(0, Employee_path.Length - 1);
                    Employee_path = Employee_path.Substring(1);
                    Employee_path = Employee_path.ToString();
                }

                if (state == 1)
                {

                    Employee_path = Employee_path.ToString();
                    if (Employee_path == null)
                    {
                        filePath = "D:\\文件解压" + "\\" + Pdfname + ".pdf";
                    }
                    else
                    {
                        filePath = Employee_path + "\\" + Pdfname + ".pdf";
                    }
                    string htmlContent = "<html>\r\n<body>\r\n    " + htmlContent2 + "\r\n</body>\r\n</html>";
                    using (var stream = new MemoryStream())
                    {
                        using (var document = new Document())
                        {
                            using (var writer = PdfWriter.GetInstance(document, stream))
                            {
                                document.Open();
                                //报表添加兰多公司的图标，固定路径（待修改成数据库搜索）
                                //图片的字节流或文件路径
                                //byte[] imageBytes = System.IO.File.ReadAllBytes("image.jpg");
                                string imagePath = "D:\\兰多备份\\陈浩明兰多报价系统\\LanDBom\\img\\Landuo_biao.png";
                                // 将图片添加到PDF文档中  
                                //Image image = Image.GetInstance(imageBytes);// 如果使用字节流
                                Image image = Image.GetInstance(imagePath); // 如果使用文件路径  
                                image.SetAbsolutePosition(420, 720); // 设置图片的位置  
                                image.ScaleToFit(130, 130);
                                writer.DirectContent.AddImage(image);
                                XMLWorkerHelper.GetInstance().ParseXHtml(writer, document, new StringReader(htmlContent));
                                document.Close();
                            }
                        }
                        System.IO.File.WriteAllBytes(filePath, stream.ToArray());
                    }
                    return File(filePath, "application/octet-stream");
                }
            }
            if (state == 2)
            {
                var listapp = Find.Find_APP(App_bian);
                var listkh = Find.Find_KH(App_bian);
                var result = from a in listapp
                             join b in listkh on a.App_Kehu equals b.CL_name into c
                             from d in c.DefaultIfEmpty()
                             select new { a.App_qishi, a.App_sho, d.CL_you, d.CL_name, d.CL_lian, a.App_dizhi, d.CL_bian, a.APP_name, a.APP_Zhong, App_bei = a == null ? "" : a.App_bei, a.AP_ren };

                JsonSerializerSettings settings = new JsonSerializerSettings();
                string jsongStr = JsonConvert.SerializeObject(result, settings);
                return Content(jsongStr, "application/json");
            }
            if (state == 3)
            {
                var listbom = Find.Find_BOM_Lv0(App_bian);
                var listnda = Find.Find_Nda();
                var list = from a in listbom
                           join b in listnda on a.BOM2_wuLiu equals b.Nd_wuLiao into c
                           from d in c.DefaultIfEmpty()
                           select new { a.BOM2_wuLiu, a.BOM2_Guige, a.BOM2_Nian, Nd_danwei = d == null ? "件" : d.Nd_danwei, Nd_jiage = d == null ? 0.00 : double.Parse(d.Nd_jiage.ToString()) };

                JsonSerializerSettings settings = new JsonSerializerSettings();
                string jsongStr = JsonConvert.SerializeObject(list, settings);

                return Content(jsongStr, "application/json");
            }
            return View();
        }

        public ActionResult Quotation_to_be_generated(string App_bian = null, string App_KHname = null, string App_xiang = null, int state = 0, string Fzr = null)
        {
            if (state == 1)
            {
                var listapp = Find.Find_APP(App_bian, App_KHname, App_xiang, Fzr);
                JsonSerializerSettings settings = new JsonSerializerSettings();
                string jsongStr = JsonConvert.SerializeObject(listapp, settings);
                return Content(jsongStr, "application/json");
            }
            return View();
        }
        //审批流
        public ActionResult ShenPi()
        {
            return View();
        }
        //主页面
        public ActionResult Zuye(int state = 0, string jidnan = "")
        {
            //汇总
            var kehu_z = db.Clirntele.ToList().Count;
            var wuliao = db.Formation.ToList().Count;
            var shen_z = db.Applications.ToList().Count;
            var xun = db.Applications.Where(x => x.App_bu == "4").ToList().Count();
            var pdf = db.Applications.Where(x => x.App_bu == "6").ToList().Count();
            var sheji = db.Designdraw.ToList().Count();
            var gongying = db.Providers.ToList().Count();
            var gonyi = db.libray.ToList().Count();
            var datime = System.DateTime.Now;
            var nian = datime.Year.ToString("D");
            var yue = datime.Month.ToString("D");
            var shun = nian + "/" + yue;
            var shen_y = db.Applications.Where(s => s.App_qishi.Contains(shun)).ToList().Count;

            var uid = MyAuthentication.GetUserId();
            var da = db.Dengli.Where(s => s.DE_ID == uid).FirstOrDefault();
            var bu = da == null ? "0" : da.DE_bumeng == null ? "0" : da.F_BuMen.FName;
            var mens = da == null ? "0" : da.DE_shenfen == null ? "0" : da.F_Shen.FName;
            Session["names_da"] = bu;
            Session["names_sa"] = mens;

            if (state == 1)
            {
                var daiban = db.Freight.Where(x => x.Fr_zhuang == "待处理").ToList();
                JsonSerializerSettings settings = new JsonSerializerSettings();
                string jsongStr = JsonConvert.SerializeObject(daiban, settings);
                return Content(jsongStr, "application/json");
            }
            if (state == 2)
            {
                var LP = new List<shenpingliu>();
                var shunf = db.shenpingliu.Where(s => s.FFirst == jidnan || s.FTwo == jidnan).ToList();
                if (shunf.Count() > 0)
                {
                    foreach (var item in shunf)
                    {
                        if (item.FFirst == jidnan)
                        {
                            if (item.FFZ == "审批中")
                            {
                                LP.Add(item);
                            }
                        }
                        if (item.FTwo == jidnan)
                        {
                            if (item.FTZ == "审批中")
                            {
                                LP.Add(item);
                            }
                        }
                    }
                }
                JsonSerializerSettings settings = new JsonSerializerSettings();
                string jsongStr = JsonConvert.SerializeObject(LP, settings);
                return Content(jsongStr, "application/json");
            }
            Session["KEhu_z"] = kehu_z;
            Session["wuliao"] = wuliao;
            Session["shen_z"] = shen_z;
            Session["shen_y"] = shen_y;

            Session["xun"] = xun;
            Session["pdf"] = pdf;
            Session["sheji"] = sheji;
            Session["gongying"] = gongying;
            Session["gonyi"] = gonyi;
            //报表
            var day = datime.Day;
            var ghj = shun + "/" + day;
            var shkfd = db.Applications.Where(s => s.App_qishi.Contains(shun)).ToList();
            ViewBag.C_App = shkfd;


            return View();
        }

        //wenjaindizi
        public ActionResult tuichu()
        {
            MyAuthentication.LoginOut();
            return RedirectToAction("Loginiew", "Login");
        }
        public ActionResult AuditFlowTesting()
        {
            var ListZBxx = FP.find_lib();
            ViewBag.ListZBxx = ListZBxx;
            ViewBag.ListPP = FP.find_manufacturer();
            ViewBag.ListZL = FP.find_ZL_();
            ViewBag.ListPO = FP.find_projectprs();
            ViewBag.parice = FP.find_parices();
            ViewBag.ro = FP.find_Rou();
            ViewBag.par = FP.find_parices();
            ViewBag.par_hc = FP.find_Rou_HC();
            return View();
        }


        public ActionResult Buffer_material()
        {
            ViewBag.par = FP.find_parices();
            return View();
        }
        public ActionResult Uploaded(int state = 1, string Uid = "")
        {
            if (state == 1)
            {
                var uid = MyAuthentication.GetUserId();
                var da = db.Dengli.Where(s => s.DE_ID == uid).First().F_BuMen.FName;
                if (da == null)
                {
                    da = "0";
                }
                var sa = db.Dengli.Where(s => s.DE_ID == uid).First().F_Shen.FName;
                if (sa == null)
                {
                    sa = "0";
                }
                Session["names_da"] = da;
                Session["names_sa"] = sa;
            }
            return View();
        }
        public ActionResult Shenqingdan()
        {
            var shunf = db.JiCheng.ToList();
            //集成检测
            foreach (var item in shunf)
            {
                var Name = item.JC_bianma;
                var client = new K3CloudApi();
                //用于记录结果
                StringBuilder Info = new StringBuilder();
                //业务对象标识
                string formId = "SAL_QUOTATION";
                string fieldKeys = "FDocumentStatus.FCaption";//审批状态
                string Filter = "FID like'%" + Name + "%'";
                //请求参数，示例使用的是SDK提供的模板类，还可以使用字符串拼接等方式
                var param = new QueryParam()
                {
                    FormId = formId,
                    FieldKeys = fieldKeys,
                    FilterString = Filter,
                    TopRowCount = 0,
                    StartRow = 0,
                    Limit = 2,
                };
                //调用接口
                var result = client.ExecuteBillQuery(param.ToJson());
                //返回的数据类型为List<List<object>>，错误的返回结果只有一行数据且IsSuccess为False
                if (result.Count == 1)
                {
                    //对返回结果进行解析和校验，这里使用的是JsonPatch
                    var resultJObject = JArray.Parse(JsonConvert.SerializeObject(result[0]));
                    var queryNode = resultJObject.SelectToken("$..IsSuccess");
                    if (queryNode != null)
                    {
                        var isSuccess = queryNode.Value<Boolean>();
                        Info.AppendLine(isSuccess ? "操作成功" : "操作失败");
                    }
                    else
                    {
                        shuf_jc(item, result);
                        Info.AppendLine("操作成功");
                    }
                }
                else if (result.Count == 0)
                {
                    shuf_jced(item, result);
                    Info.AppendLine("操作成功");
                }
                else
                {
                    //修改
                    shuf_jc(item, result);
                    Info.AppendLine("操作成功");
                }
            }
            var shufn = db.JiCheng.ToList();
            ViewBag.shunf = shufn;
            return View();
        }

        private void shuf_jc(JiCheng item, List<List<object>> result)
        {
            for (int i = 0; i < result.Count; i++)
            {
                item.JC_zhuang = (string)result[i][0];
                db.Entry(item).State = EntityState.Modified;
            }
            int shun = db.SaveChanges();
        }

        private void shuf_jced(JiCheng item, List<List<object>> result)
        {

            item.JC_zhuang = "已删除";
            db.Entry(item).State = EntityState.Modified;
            int sun = db.SaveChanges();
        }
        public ActionResult sjdnww(string App_bian = "", string App_KHname = "", string App_xiang = "", int state = 0, string jsokk = "")
        {
            if (state == 1)
            {
                var shunf = FP.shenqin(App_bian, App_KHname, App_xiang);
                var list = from a in shunf select new { a.JC_bianma, a.JC_bianhao, a.JC_zhuang, a.JC_shi, a.JC_shen };
                JsonSerializerSettings settings = new JsonSerializerSettings();
                string jsongStr = JsonConvert.SerializeObject(list, settings);
                return Content(jsongStr, "application/json");
            }
            if (state == 2)
            {
                var FHUNS = db.JiCheng.FirstOrDefault(s => s.JC_bianhao == App_bian);
                var Bian = FHUNS.JC_Yuan;
                var shunf = db.Applications.FirstOrDefault(s => s.AP_bian == Bian);
                var shinf = double.Parse(shunf.App_BanB);
                var shifn = Math.Truncate(shinf) + 1;
                shunf.App_BanB = shifn.ToString();
                shunf.App_bu = "4";
                int sun = db.SaveChanges();
                string message = "";
                if (sun > 0)
                {
                    var BOmi = db.BOMini.Where(b => b.BOM2_bian == Bian && b.BOM2_Zhuang == shinf.ToString()).ToList();
                    foreach (var b in BOmi)
                    {
                        b.BOM2_Zhuang = shifn.ToString();
                        db.BOMini.Add(b);
                    }
                    int font = db.SaveChanges();
                    if (font > 0)
                    {
                        var poklmj = db.JiCheng.FirstOrDefault(w => w.JC_bianma == jsokk);
                        poklmj.JC_ung = "重新审核中";
                        db.Entry(poklmj).State = EntityState.Modified;
                        int desaim = db.SaveChanges();
                    }
                    message = font > 0 ? "修改成功" : "修改失败";
                }
                else
                {
                    message = "系统报错！请重试";
                }
                return Content(message);
            }
            if (state == 3)
            {
                var FHUNS = db.JiCheng.FirstOrDefault(s => s.JC_bianhao == App_bian);
                var Bian = FHUNS.JC_Yuan;
                var shunf = db.Applications.FirstOrDefault(w => w.AP_bian == Bian);
                var zkoamg = shunf.App_bu;
                return Content(zkoamg);
            }

            return View();
        }
        //小版本变更
        public ActionResult XiaobanUP(int stated = 0, string bian = "")
        {
            if (stated == 2)
            {
                var shunf = db.Applications.FirstOrDefault(s => s.AP_bian == bian);
                var shinf = int.Parse(shunf.App_BanB);
                var sjidml = int.Parse(shunf.App_bu);
                var shifn = shinf + 0.1;
                var shisd = sjidml - 1;
                shunf.App_BanB = shifn.ToString();
                shunf.App_bu = shisd.ToString();
                int sun = db.SaveChanges();
                string message = "";
                if (sun > 0)
                {
                    var BOmi = db.BOMini.Where(b => b.BOM2_bian == bian && b.BOM2_Zhuang == shinf.ToString()).ToList();
                    foreach (var b in BOmi)
                    {
                        b.BOM2_Zhuang = shifn.ToString();
                        db.BOMini.Add(b);
                    }
                    int font = db.SaveChanges();
                    message = font > 0 ? "修改成功" : "修改失败";
                }
                else
                {
                    message = "系统报错！请重试";
                }
                return Content(message);
            }
            return View();
        }


        public JsonResult Modify_fre_zhuang(List<Freight> modle)
        {
            Modlfy_Contracts MC = new Modlfy_Contracts();
            int rows = 0;
            foreach (Freight i in modle)
            {
                if (modle == null)
                {
                    modle = new List<Freight>();
                }
                Freight inf = new Freight()
                {
                    Fr_id = i.Fr_id,
                    Fr_zhuang = i.Fr_zhuang,
                };
                rows += MC.Changefr(inf);
            }

            string message = "修改" + rows + "条";
            return Json(message);
        }

        protected override void OnActionExecuting(ActionExecutingContext filterContext)
        {
            var cookie = Request.Cookies["Language"];
            if (cookie != null)
            {
                var cultureName = cookie.Value;
                Thread.CurrentThread.CurrentCulture = new CultureInfo(cultureName);
                Thread.CurrentThread.CurrentUICulture = new CultureInfo(cultureName);
            }
            base.OnActionExecuting(filterContext);
        }
        public ActionResult SetLanguage(string culture)
        {
            // 将选择的语言存储到会话中  
            Session["Language"] = culture;

            // 重定向回主页，并带上新的语言设置  
            return RedirectToAction("Zuye", new { culture = culture });
        }

        public ActionResult GYBOM(string SHun, string AP_bian = "", string App_Kehu = "", string App_JTime = "", int states = 0, int id = 0)
        {
            List<Applications> app = db.Applications.Where(s => s.AP_bian.Contains("ERP")).OrderByDescending(s => s.App_id).ToList();
            ViewBag.C_App = app;
            var listApp = find_APP.find_app_infor111(AP_bian, App_Kehu, App_JTime);
            if (states == 1)
            {
                var list = from s in listApp
                           select new { s.App_id, s.App_Kehu, s.App_qishi, s.App_JTime, s.AP_bian, s.APP_name, s.App_bu, s.APP_fenL, s.App_zhand, s.AP_ren, s.App_bei };
                JsonSerializerSettings settings = new JsonSerializerSettings();
                string jsongStr = JsonConvert.SerializeObject(list, settings);
                return Content(jsongStr, "application/json");
            }
            if (states == 2)
            {
                Applications applications = db.Applications.Where(x => x.App_id == id).FirstOrDefault();
                int index = 0;
                string zhuang = "删除失败";
                string Bian = applications.AP_bian;
                if (applications != null)
                {
                    db.Applications.Remove(applications);
                    index += db.SaveChanges();
                    //删除物料
                    List<BOMini> Bom = db.BOMini.Where(s => s.BOM2_bian == Bian).ToList();
                    foreach (BOMini item in Bom)
                    {
                        string WLB = item.BOM2_liao;
                        Formation HN = db.Formation.Where(s => s.FM_bai == WLB).FirstOrDefault();
                        db.Formation.Remove(HN);db.SaveChanges();
                    }
                    
                }
                if (index != 0)
                {
                    zhuang = "删除成功";
                }
                return Content(zhuang);
            }
            return View();
        }
        public ActionResult cghangapk(string fgggh, string name)
        {
            int temp = 0;
            Applications app = db.Applications.Where(x => x.AP_bian == fgggh).FirstOrDefault();
            if (app != null)
            {
                app.AP_ren = name;
                app.App_fzr = name;
                temp += db.SaveChanges();
            }
            return Content(temp.ToString());
        }
        //获取Excel文件并打开
        [HttpPost]
        public ActionResult ExcelFilePath(HttpPostedFileBase file)
        {
            var path = "";
            try
            {
                //解决方案 先保存到服务器后使用
                if (file != null && file.ContentLength > 0)
                {
                    string bian = "";
                    // 指定保存文件的路径（服务器上的实际路径）  
                    path = Server.MapPath("~/Files/");
                    // 确保目录存在  
                    Directory.CreateDirectory(path);

                    // 生成唯一的文件名（这里只是简单示例，你可能需要更复杂的唯一性逻辑）  
                    string fileName = file.FileName;//只保存一个文件 有文件就删除

                    // 保存文件到服务器  
                    string filePath = Path.Combine(path, fileName);
                    file.SaveAs(filePath);//真实路径
                    System.Data.DataTable table = ExcelToDatatable(filePath, "Sheet1", true);


                    //自定义一个datatable
                    var table1 = new System.Data.DataTable();
                    table1.Columns.Add("BOM2_chen", typeof(string));
                    table1.Columns.Add("BOM2_duan", typeof(string));
                    table1.Columns.Add("BOM2_wuLiu", typeof(string));
                    table1.Columns.Add("BOM2_liao", typeof(string));
                    table1.Columns.Add("FM_long", typeof(string));
                    table1.Columns.Add("FM_kuan", typeof(string));
                    table1.Columns.Add("FM_gao", typeof(string));
                    table1.Columns.Add("BOM2_Nian", typeof(string));
                    table1.Columns.Add("BOM2_Yong", typeof(string));
                    table1.Columns.Add("BOM2_fenzi", typeof(string));
                    table1.Columns.Add("BOM2_Lei", typeof(string));
                    table1.Columns.Add("BOM2_mioa", typeof(string));
                    table1.Columns.Add("BOM2_Jin", typeof(string));//原材料
                    table1.Columns.Add("BOM2_Sone", typeof(string));
                    table1.Columns.Add("BOM2_ST", typeof(string));
                    table1.Columns.Add("BOM2_STeds", typeof(string));
                    //去除空值
                    int count = db.Applications.Count();
                    int cossj = db.Applications.Where(x => x.AP_bian.Contains("ERP")).GroupBy(d => new { d.App_id }).Select(a => a.OrderByDescending(b => b.App_id).ElementAt(0)).FirstOrDefault().App_id;
                    cossj++;
                    var app = new Applications();
                    bian = "ERP" + cossj.ToString();
                    app.AP_bian = "ERP" + cossj.ToString();
                    app.App_bu = "3";
                    app.App_bei = "保存";
                    db.Applications.Add(app);
                    db.SaveChanges();
                    int iss = 1000;
                    string ddd = "";
                    int hgd = 0;
                    int temppp = 0;
                    char[] letter = { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        DataRow dataRow = table.Rows[i];
                        if (dataRow["BOM2_chen"].ToString() != "")
                        {
                            if (dataRow["BOM2_liao"].ToString() == "")
                            {
                                var bom = db.BOMini.Where(x => x.BOM2_bian == bian).ToList();
                                if (bom.Count > 0)
                                {
                                    foreach (BOMini bOMini in bom)
                                    {
                                        db.BOMini.Remove(bOMini);
                                    }
                                }
                                Applications assd = db.Applications.Where(x => x.AP_bian == bian).FirstOrDefault();
                                if (assd != null)
                                {
                                    db.Applications.Remove(assd);
                                }
                                db.SaveChanges();
                                return Content("料号为空");
                            }

                            var bomin = dataRow["BOM2_wuLiu"].ToString();//物料号
                            var liao = dataRow["BOM2_liao"].ToString();//料号
                            var KK = dataRow["BOM2_STeds"].ToString().Split(' ');
                            var Jiehuu = "";
                            for (int c = 0; c < KK.Length; c++)
                            {
                                if (Jiehuu == "")
                                {
                                    Jiehuu = KK[c];
                                }
                                else
                                {
                                    Jiehuu = Jiehuu + "," + KK[c];
                                }
                            }
                            var fojiwn = db.Formation.Where(s => s.FM_bai == liao).ToList();

                            if (temppp == 0)
                            {
                                ddd = iss.ToString();
                                temppp++;
                            }
                            else
                            {
                                string cen = dataRow["BOM2_chen"].ToString();

                                if (cen == "Leve0")
                                {
                                    iss = iss * temppp + 1;
                                    ddd = iss.ToString();
                                    temppp++;
                                    hgd = 0;
                                }
                                else if (cen == "leve1")
                                {
                                    iss = iss * temppp;
                                    ddd = iss.ToString();
                                    string ddsd = letter[hgd].ToString();
                                    ddd = ddd + "-" + ddsd;
                                    hgd++;
                                }
                                else
                                {
                                    ddd = ddd + "-" + "a";

                                }

                            }
                            string BOM2_Sone = dataRow["BOM2_Sone"].ToString();

                            if (BOM2_Sone == "纸制品")
                            {
                                BOM2_Sone = "1";
                            }
                            if (BOM2_Sone == "木制品")
                            {
                                BOM2_Sone = "2";
                            }
                            if (BOM2_Sone == "塑料制品")
                            {
                                BOM2_Sone = "3";
                            }
                            if (BOM2_Sone == "金属制品")
                            {
                                BOM2_Sone = "4";
                            }
                            if (BOM2_Sone == "布")
                            {
                                BOM2_Sone = "5";
                            }
                            if (BOM2_Sone == "胶粘制品")
                            {
                                BOM2_Sone = "6";
                            }
                            if (BOM2_Sone == "其他")
                            {
                                BOM2_Sone = "7";
                            }
                            if (BOM2_Sone == "套件")
                            {
                                BOM2_Sone = "8";
                            }
                            if (fojiwn.Count == 0)
                            {
                                var inde = db.Formation.ToList();
                                var S = "";
                                for (int j = inde.Count - 1; j < inde.Count; j++)
                                {
                                    var A = inde[j].FM_bai;
                                    S = A.Substring(2);
                                }
                                var shund = new Formation();
                                shund.FM_bai = dataRow["BOM2_liao"].ToString();
                                shund.FM_name = bomin;
                                shund.FM_long = (decimal?)double.Parse(dataRow["FM_long"].ToString());
                                shund.FM_kuan = (decimal?)double.Parse(dataRow["FM_kuan"].ToString());
                                shund.FM_gao = (decimal?)double.Parse(dataRow["FM_gao"].ToString());
                                shund.FM_ji = dataRow["BOM2_fenzi"].ToString();
                                shund.FM_Zhuang = dataRow["BOM2_Lei"].ToString();
                                shund.FM_gg = dataRow["FM_long"].ToString() + "*" + dataRow["FM_kuan"].ToString() + "*" + dataRow["FM_gao"].ToString();
                                shund.FM_nyl = dataRow["BOM2_Nian"].ToString();
                                shund.FM_ycl = dataRow["BOM2_Jin"].ToString();
                                shund.FM_ms = dataRow["BOM2_mioa"].ToString();
                                shund.FM_yj = dataRow["BOM2_Sone"].ToString();
                                shund.FM_ej = dataRow["BOM2_ST"].ToString();
                                shund.FM_Jieti = Jiehuu;
                                db.Formation.Add(shund);
                                db.SaveChanges();

                                string lh = dataRow["BOM2_liao"].ToString();
                                Formation formation = db.Formation.Where(x => x.FM_bai == lh).FirstOrDefault();

                                var bom = new BOMini();
                                bom.BOM2_bian = bian;
                                bom.Fo_id = formation.FM_ID;
                                bom.BOM2_chen = dataRow["BOM2_chen"].ToString();
                                bom.BOM2_wuLiu = dataRow["BOM2_wuLiu"].ToString();
                                bom.BOM2_liao = dataRow["BOM2_liao"].ToString();
                                bom.BOM2_Guige = dataRow["FM_long"].ToString() + "mm*" + dataRow["FM_kuan"].ToString() + "mm*" + dataRow["FM_gao"].ToString() + "mm";
                                bom.BOM2_Nian = dataRow["BOM2_Nian"].ToString();
                                bom.BOM2_Yong = dataRow["BOM2_Yong"].ToString();
                                bom.BOM2_fenzi = dataRow["BOM2_fenzi"].ToString();
                                bom.BOM2_fenzi = dataRow["BOM2_Yong"].ToString();
                                bom.BOM2_Lei = dataRow["BOM2_Lei"].ToString();
                                bom.BOM2_BQing = ddd;
                                bom.BOM2_Sone = BOM2_Sone;
                                bom.BOM2_ST = dataRow["BOM2_ST"].ToString();
                                db.BOMini.Add(bom);
                                db.SaveChanges();

                            }
                            else
                            {
                                string lh = dataRow["BOM2_liao"].ToString();
                                Formation formation = db.Formation.Where(x => x.FM_bai == lh).FirstOrDefault();

                                var bom = new BOMini();
                                bom.BOM2_bian = bian;
                                bom.Fo_id = formation.FM_ID;
                                bom.BOM2_chen = dataRow["BOM2_chen"].ToString();
                                bom.BOM2_wuLiu = dataRow["BOM2_wuLiu"].ToString();
                                bom.BOM2_liao = dataRow["BOM2_liao"].ToString();
                                bom.BOM2_Guige = dataRow["FM_long"].ToString() + "mm*" + dataRow["FM_kuan"].ToString() + "mm*" + dataRow["FM_gao"].ToString() + "mm";
                                bom.BOM2_Nian = dataRow["BOM2_Nian"].ToString();
                                bom.BOM2_Yong = dataRow["BOM2_Yong"].ToString();
                                bom.BOM2_fenzi = dataRow["BOM2_fenzi"].ToString();
                                bom.BOM2_fenzi = dataRow["BOM2_Yong"].ToString();
                                bom.BOM2_Lei = dataRow["BOM2_Lei"].ToString();
                                bom.BOM2_BQing = ddd;
                                bom.BOM2_Sone = BOM2_Sone;
                                bom.BOM2_ST = dataRow["BOM2_ST"].ToString();
                                db.BOMini.Add(bom);
                                db.SaveChanges();
                            }
                        }
                    }
                    return Content(bian);
                }
                else
                {
                    // 返回失败消息  
                    return Json(new { success = false, message = "没有文件被上传或者文件大小为0." });
                }
            }
            catch (Exception)
            {
                return Json(new { Success = false, Message = "系统错误！" });
            }

            return View();
        }
        public static System.Data.DataTable ExcelToDatatable(string fileName, string sheetName, bool isFirstRowColumn)
        {
            System.Data.DataTable data = new System.Data.DataTable();
            int startRow = 0;
            int cellCount = 0;//列数
            int rowCount = 0;//行数
            try
            {
                using (var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    // 创建HSSFWorkbook或XSSFWorkbook对象，取决于你的Excel文件格式  
                    IWorkbook workbook;
                    if (System.IO.Path.GetExtension(fileName).Equals(".xls"))
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    else
                    {
                        workbook = new XSSFWorkbook(fs);
                    }

                    // 获取第一个工作表  
                    ISheet sheet = workbook.GetSheetAt(0);
                    if (sheet != null)
                    {
                        IRow firstRow = sheet.GetRow(0);
                        cellCount = firstRow.LastCellNum; //第一行最后一个cell的编号 即总的列数
                        if (isFirstRowColumn)//如果第一行是标题行
                        {
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)//第一行列数循环
                            {
                                DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);//获取标题
                                data.Columns.Add(column);//添加列
                            }
                            startRow = sheet.FirstRowNum + 2;//1（即第二行，第一行0从开始）
                        }
                        else
                        {
                            startRow = sheet.FirstRowNum;//0
                        }
                        //最后一行的标号
                        rowCount = sheet.LastRowNum;
                        for (int i = startRow; i <= rowCount; ++i)//循环遍历所有行
                        {
                            IRow row = sheet.GetRow(i);//第几行
                            if (row != null)
                            {
                                //将excel表每一行的数据添加到datatable的行中
                                DataRow dataRow = data.NewRow();
                                for (int j = row.FirstCellNum; j < cellCount; ++j)
                                {
                                    if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                    {
                                        dataRow[j] = row.GetCell(j).ToString();
                                    }
                                }
                                data.Rows.Add(dataRow);
                            }
                            else
                            {
                                continue; //没有数据的行默认是null;
                            }

                        }
                    }
                    return data;
                }
            }
            catch (Exception)
            {
                return data;
            }
        }
        public ActionResult DownloadExce3(List<Applications> Designdraws, int year = 0)
        {
            int state = 1;
            if (state == 1)
            {
                string year2 = year.ToString();
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                using (ExcelPackage excel = new ExcelPackage())
                {
                    // 添加工作表
                    var worksheet = excel.Workbook.Worksheets.Add("Sheet1");
                    // 写入数据
                    worksheet.Cells[1, 1].Value = "物料名";
                    worksheet.Cells[1, 2].Value = "物料名";
                    worksheet.Cells[1, 3].Value = "一月";
                    worksheet.Cells[1, 4].Value = "二月";
                    worksheet.Cells[1, 5].Value = "三月";
                    worksheet.Cells[1, 6].Value = "四月";
                    worksheet.Cells[1, 7].Value = "五月";
                    worksheet.Cells[1, 8].Value = "六月";
                    worksheet.Cells[1, 9].Value = "七月";
                    worksheet.Cells[1, 10].Value = "八月";
                    worksheet.Cells[1, 11].Value = "九月";
                    worksheet.Cells[1, 12].Value = "十月";
                    worksheet.Cells[1, 13].Value = "十一月";
                    worksheet.Cells[1, 14].Value = "十二月";
                    int i = 2;

                    foreach (Applications des in Designdraws)
                    {
                        List<LanJiLu> lj = db.LanJiLu.Where(x => x.JL_Fen2 == des.AP_bian).ToList();
                        lj = lj.GroupBy(d => new { d.JL_Name, d.LI_KZ }).Select(a => a.OrderBy(b => b.JL_ID).ElementAt(0)).ToList();

                        foreach (LanJiLu l in lj)
                        {
                            int jsj = 1;
                            List<LanJiLu> lj2 = db.LanJiLu.Where(x => x.JL_Name == l.JL_Name && x.LI_KZ == l.LI_KZ && x.JL_Y == year2).ToList();
                            foreach (LanJiLu l2 in lj2)
                            {
                                if (jsj == 1)
                                {
                                    if (l2.LI_KZ != null)
                                    {
                                        worksheet.Cells[i, 1].Value = l2.LI_KZ + "克" + l2.JL_Name;
                                    }
                                    else
                                    {
                                        worksheet.Cells[i, 1].Value = l2.JL_Name;
                                    }
                                    jsj++;
                                }
                                if (jsj == 2)
                                {
                                    worksheet.Cells[i, 2].Value = l2.JL_Fen2;
                                }
                                int index = int.Parse(l2.JL_M) + 2;
                                worksheet.Cells[i, index].Value = l2.JL_Price;
                            }
                            i++;
                        }


                        worksheet.Row(i).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
                        worksheet.Row(i).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    }
                    worksheet.Cells["A:Z"].AutoFitColumns();
                    worksheet.Column(19).Width = 40;
                    worksheet.Column(18).Width = 40;
                    string path = "";

                    path = Server.MapPath("~/Files/");
                    string Name = year + "年物料更新记录" + ".xlsx";
                    // 确保目录存在  
                    Directory.CreateDirectory(path);

                    // 生成唯一的文件名（这里只是简单示例，你可能需要更复杂的唯一性逻辑）  
                    string fileName = Path.GetFileNameWithoutExtension(Name) + "_" + Guid.NewGuid() + Path.GetExtension(Name);

                    // 保存文件到服务器  
                    string filePath = Path.Combine(path, fileName);
                    excel.SaveAs(filePath);
                    return Json(filePath);
                }

            }
            return View();
        }

        public ActionResult DownloadExce2(List<Applications> Designdraws, int year = 0)
        {
            int state = 1;
            System.Data.DataTable dataTable = new System.Data.DataTable();
            dataTable.Columns.Add("name");
            dataTable.Columns.Add("lei");
            dataTable.Columns.Add("a");
            dataTable.Columns.Add("b");
            dataTable.Columns.Add("c");
            dataTable.Columns.Add("d");
            dataTable.Columns.Add("e");
            dataTable.Columns.Add("f");
            dataTable.Columns.Add("g");
            dataTable.Columns.Add("h");
            dataTable.Columns.Add("i");
            dataTable.Columns.Add("j");
            dataTable.Columns.Add("k");
            dataTable.Columns.Add("l");

            if (state == 1)
            {
                dataTable.Rows.Clear();
                string year2 = year.ToString();

                foreach (Applications des in Designdraws)
                {
                    List<LanJiLu> lj = db.LanJiLu.Where(x => x.JL_Fen2 == des.AP_bian && x.JL_Y == year2).ToList();
                    lj = lj.GroupBy(d => new { d.JL_Name, d.LI_KZ }).Select(a => a.OrderBy(b => b.JL_ID).ElementAt(0)).ToList();

                    foreach (LanJiLu l in lj)
                    {
                        int jsj = 1;
                        List<LanJiLu> lj2 = db.LanJiLu.Where(x => x.JL_Name == l.JL_Name && x.LI_KZ == l.LI_KZ && x.JL_Y == year2).ToList();
                        DataRow row = dataTable.NewRow();
                        foreach (LanJiLu l2 in lj2)
                        {

                            if (jsj == 1)
                            {
                                if (l2.LI_KZ != null)
                                {
                                    row["name"] = l2.LI_KZ + "克" + l2.JL_Name;
                                }
                                else
                                {
                                    row["name"] = l2.JL_Name;
                                }
                                jsj++;
                            }
                            if (jsj == 2)
                            {
                                row[1] = l2.JL_Fen2;
                                jsj++;
                            }
                            int index = int.Parse(l2.JL_M) + 1;
                            row[index] = l2.JL_Price;
                        }

                        dataTable.Rows.Add(row);
                    }

                }
                // 遍历DataTable中的每一行  
                foreach (DataRow row in dataTable.Rows)
                {
                    // 遍历每一行的每一个字段  
                    for (int i = 0; i < dataTable.Columns.Count; i++)
                    {
                        // 检查字段值是否为空（包括null和DBNull）  
                        if (row.IsNull(i) || row[i] == DBNull.Value)
                        {
                            row[i] = "未更新"; // 替换为0   
                        }
                    }
                }

            }

            JsonSerializerSettings settings = new JsonSerializerSettings();
            string jsongStr = JsonConvert.SerializeObject(dataTable, settings);
            return Content(jsongStr, "application/json");
        }
        public ActionResult Record_statement()
        {
            return View();
        }

        #region 保存查询条件
        public ActionResult BaoCunTJ(List<BcunJl> bcunJls, BcunJl bcun, int state = 0)
        {
            //保存
            if (state == 0)
            {
                if (bcunJls == null)
                {
                 
                    bcunJls = new List<BcunJl>();
                }
                int shun = 0;
                foreach (var item in bcunJls)
                {
                    var shunf = db.BcunJl.Where(s => s.BO_name == item.BO_name && s.BO_DJM == item.BO_DJM && s.BO_ZDID == item.BO_ZDID).FirstOrDefault();
                    if (shunf != null)
                    {
                        shunf.BO_VAL = item.BO_VAL;
                        db.Entry(shunf).State = EntityState.Modified;
                        shun += db.SaveChanges();
                    }
                    else
                    {
                        db.BcunJl.Add(item);
                        shun += db.SaveChanges();
                    }
                }
                string message = shun >0 ? "Y" : "N";
                return Content(message);
            }
            //查询
            if (state == 1)
            {
                var shunf = db.BcunJl.Where(s => s.BO_name == bcun.BO_name && s.BO_DJM == bcun.BO_DJM).ToList();
                JsonSerializerSettings settings = new JsonSerializerSettings();
                string jsongStr = JsonConvert.SerializeObject(shunf, settings);
                return Content(jsongStr, "application/json");
            }
            return View();
        }

        #endregion
    }

}
